﻿using System;
using System.Data;
using System.Data.Common;
using SummerSmile.Common.Utils.Data.DBFactory;
using System.Configuration;

namespace SummerSmile.Common.Utils.Data
{
    public class DBOperator:IDBOperator
    {
        #region 字段
        private IDBFactory _dbFactory;
        private IDbConnection _dbConn;
        private IDbCommand _dbComm;
        private IDbTransaction _dbTransaction = null;
        private DBProviderType _dbProviderType;
        private bool _inTransaction = false;

        #endregion

        #region 属性
        /// <summary>
        /// 数据库工厂
        /// </summary>
        public IDBFactory DbFactory
        {
            get { return _dbFactory; }
            set { _dbFactory = value; }
        }
        /// <summary>
        /// 数据库连接
        /// </summary>
        public IDbConnection DbConn
        {
            get { return _dbConn; }
            set { _dbConn = value; }
        }
        /// <summary>
        /// 数据库命令
        /// </summary>
        public IDbCommand DbComm
        {
            get { return _dbComm; }
            set { _dbComm = value; }
        }
        /// <summary>
        /// 数据库驱动类型
        /// </summary>
        public DBProviderType DbProviderType
        {
            get { return _dbProviderType; }
            set { _dbProviderType = value; }
        }
        /// <summary>
        /// 数据库执行的事务
        /// </summary>
        public IDbTransaction DbTransaction
        {
            get { return _dbTransaction; }
            set { _dbTransaction = value; }
        }
        /// <summary>
        /// 是否已在事务之中
        /// </summary>
        public bool InTransaction
        {
            get { return _inTransaction; }
            set { _inTransaction = value; }
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造数据库操作类对象
        /// 默认参数：配置文件中名称为[ConnectionString]的数据库配置项
        /// 类似：<add name="ConnectionString" providerName="Oracle" connectionString="User ID=sdb;Data Source=orcl;Password=sdb"/>
        /// providerName项为：SqlServer、SQLite、OleDB、Oracle、MySQL其中之一
        /// </summary>
        public DBOperator()
        {
            CreateDbHelperInstance(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, GetDBProviderType(ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName));
        }
        /// <summary>
        /// 构造数据库操作类对象
        /// 默认参数：数据库驱动类型为SqlServer
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public DBOperator(string connectionString)
        {
            CreateDbHelperInstance(connectionString, DBProviderType.SQL);
        }
        /// <summary>
        /// 构造数据库操作类对象
        /// 默认参数：配置文件中名称为[ConnectionString]的数据库配置项
        /// 类似：<add name="ConnectionString" providerName="Oracle" connectionString="User ID=sdb;Data Source=orcl;Password=sdb"/>
        /// </summary>
        /// <param name="dbProviderType">数据库驱动类型，包含：SqlServer、SQLite、OleDB、Oracle、MySQL</param>
        public DBOperator(DBProviderType dbProviderType)
        {
            CreateDbHelperInstance(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString, dbProviderType);
        }
        /// <summary>
        /// 构造数据库操作类对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="dbProviderType">数据库驱动类型，包含：SqlServer、SQLite、OleDB、Oracle、MySQL</param>
        public DBOperator(string connectionString, DBProviderType dbProviderType)
        {
            CreateDbHelperInstance(connectionString, dbProviderType);
        }
        #endregion

        #region 初始化
        /// <summary>
        /// 初始化程序对数据源类的实现的DBHelper实例
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="dbProviderType">数据库驱动类型，包含：SqlServer、SQLite、OleDB、Oracle、MySQL</param>
        public void CreateDbHelperInstance(string connectionString, DBProviderType dbProviderType)
        {
            this.DbProviderType = dbProviderType;
            CreateFactory();
            this.DbConn = this.DbFactory.CreateConnection();
            this.DbConn.ConnectionString = connectionString;
            this.DbComm = this.DbConn.CreateCommand();
        }

        private void CreateFactory()
        {
            switch (this.DbProviderType)
            {
                case DBProviderType.SQL:
                    this.DbFactory = new SqlFactory();
                    break;
                case DBProviderType.SQLite:
                    this.DbFactory = new SqliteFactory();
                    break;
                case DBProviderType.OleDB:
                    this.DbFactory = new OleDBFactory();
                    break;
                case DBProviderType.Oracle:
                    this.DbFactory = new OracleFactory();
                    break;
                case DBProviderType.MySQL:
                    this.DbFactory = new MySqlFactory();
                    break;
            }
        }

        public static  DBProviderType GetDBProviderType(string providerName)
        {
            DBProviderType dbProviderType;
            switch (providerName)
            {

                case "System.Data.SQLite":
                    dbProviderType = DBProviderType.SQLite;
                    break;
                case "OleDB":
                    dbProviderType = DBProviderType.OleDB;
                    break;
                case "System.Data.OracleClient":
                case "Oracle.DataAccess.Client"://NET 2.0
                    dbProviderType = DBProviderType.Oracle;
                    break;
                case "MySql.Data.MySqlClient":
                    dbProviderType = DBProviderType.MySQL;
                    break;
                case "System.Data.SqlClient":
                default:
                    dbProviderType = DBProviderType.SQL;
                    break;

            }
            return dbProviderType;
        }
        #endregion

        #region 公共方法
        public bool OpenConnection()
        {
            if (_dbConn.State == ConnectionState.Closed)
            {
                try
                {
                    _dbComm.Connection = _dbConn;
                    _dbConn.Open();
                }
                catch
                {
                    return false;
                }
            }

            return true;
        }

        #region Transaction
        /// <summary>
        /// 事务开始
        /// </summary>
        /// <returns></returns>
        public IDbTransaction BeginTransaction()
        {
            if (!this.InTransaction)
            {
                this.InTransaction = true;
                this.DbTransaction = this.DbConn.BeginTransaction();
            }
            return this.DbTransaction;
        }
        /// <summary>
        /// 递交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (this.InTransaction)
            {
                // 事务已经完成了，一定要更新标志信息
                this.InTransaction = false;
                this.DbTransaction.Commit();
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (this.InTransaction)
            {
                this.InTransaction = false;
                this.DbTransaction.Rollback();
            }
        }
        #endregion

        public void CloseConnection()
        {
            if (this._dbConn != null)
            {
                if (this._dbConn.State == ConnectionState.Open)
                {
                    this._dbConn.Close();
                    this._dbConn.Dispose();
                    this._dbComm.Dispose();
                }

                this._dbConn = null;
                this._dbComm = null;
                this._dbFactory = null;
                this._dbTransaction = null;
            }
        }

        #region  ExecuteReader
        public IDataReader ExecuteReader(string commandText)
        {
            return this.ExecuteReader(commandText, null);
        }

        public IDataReader ExecuteReader(string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            return this.ExecuteReader(CommandType.Text, commandText, dbParameters);
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            if (!OpenConnection()) return null;
            this.DbComm.CommandText = commandText;
            this.DbComm.CommandType = commandType;
            if (this.InTransaction)
            {
                this.DbComm.Transaction = this.DbTransaction;
            }

            if (dbParameters != null)
            {
                this.DbComm.Parameters.Clear();
                for (int i = 0; i < dbParameters.Length; i++)
                {
                    if (dbParameters[i] != null)
                    {
                        this.DbComm.Parameters.Add(dbParameters[i]);
                    }
                }
            }
            IDataReader dataReader = this.DbComm.ExecuteReader(CommandBehavior.CloseConnection);
            return dataReader;

        }
        #endregion

        #region ExecuteNonQuery
        public int ExecuteNonQuery(string commandText)
        {
            return this.ExecuteNonQuery(commandText, null);
        }

        public int ExecuteNonQuery(string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            return this.ExecuteNonQuery(CommandType.Text, commandText, dbParameters);
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            if (!OpenConnection()) return -1;
            this.DbComm.CommandText = commandText;
            this.DbComm.CommandType = commandType;
            if (this.InTransaction)
            {
                this.DbComm.Transaction = this.DbTransaction;
            }

            if (dbParameters != null)
            {
                this.DbComm.Parameters.Clear();
                for (int i = 0; i < dbParameters.Length; i++)
                {
                    if (dbParameters[i] != null)
                    {
                        this.DbComm.Parameters.Add(dbParameters[i]);
                    }
                }
            }

            int returnValue = this.DbComm.ExecuteNonQuery();
            return returnValue;
        }
        #endregion

        #region ExecuteScalar
        public object ExecuteScalar(string commandText)
        {
            return this.ExecuteScalar(commandText, null);
        }

        public object ExecuteScalar(string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            return this.ExecuteScalar(CommandType.Text, commandText, dbParameters);
        }

        public object ExecuteScalar(CommandType commandType, string commandText, System.Data.Common.DbParameter[] dbParameters)
        {
            if (!OpenConnection()) return null;
            this.DbComm.CommandText = commandText;
            this.DbComm.CommandType = commandType;
            if (this.InTransaction)
            {
                this.DbComm.Transaction = this.DbTransaction;
            }

            if (dbParameters != null)
            {
                this.DbComm.Parameters.Clear();
                for (int i = 0; i < dbParameters.Length; i++)
                {
                    if (dbParameters[i] != null)
                    {
                        this.DbComm.Parameters.Add(dbParameters[i]);
                    }
                }
            }

            object result = this.DbComm.ExecuteScalar();
            return result;
        }
        #endregion

        #region Fill DataTable
        public DataTable Fill(DataTable dataTable, string commandText)
        {
            return this.Fill(dataTable, commandText, null);
        }

        public DataTable Fill(DataTable dataTable, string commandText, DbParameter[] dbParameters)
        {
            return this.Fill(dataTable, CommandType.Text, commandText, dbParameters);
        }

        public DataTable Fill(DataTable dataTable, CommandType commandType, string commandText, DbParameter[] dbParameters)
        {
            if (!OpenConnection()) return null;
            this.DbComm.CommandText = commandText;
            this.DbComm.CommandType = commandType;
            if (this.InTransaction)
            {
                this.DbComm.Transaction = this.DbTransaction;
            }

            if (dbParameters != null)
            {
                this.DbComm.Parameters.Clear();
                for (int i = 0; i < dbParameters.Length; i++)
                {
                    if (dbParameters[i] != null)
                    {
                        this.DbComm.Parameters.Add(dbParameters[i]);
                    }
                }
            }
            DbDataAdapter adapter = (DbDataAdapter)this.DbFactory.CreateDataAdapter(this.DbComm);
            adapter.Fill(dataTable);
            return dataTable;
        }
        #endregion

        #region Fill DataSet
        public DataSet Fill(DataSet dataSet, string commandText, string tableName)
        {
            return this.Fill(dataSet, commandText, null, tableName);
        }

        public DataSet Fill(DataSet dataSet, string commandText, DbParameter[] dbParameters, string tableName)
        {
            return this.Fill(dataSet, CommandType.Text, commandText, dbParameters, tableName);
        }

        public DataSet Fill(DataSet dataSet, CommandType commandType, string commandText, DbParameter[] dbParameters, string tableName)
        {
            if (!OpenConnection()) return null;
            this.DbComm.CommandText = commandText;
            this.DbComm.CommandType = commandType;
            if (this.InTransaction)
            {
                this.DbComm.Transaction = this.DbTransaction;
            }

            if (dbParameters != null)
            {
                this.DbComm.Parameters.Clear();
                for (int i = 0; i < dbParameters.Length; i++)
                {
                    if (dbParameters[i] != null)
                    {
                        this.DbComm.Parameters.Add(dbParameters[i]);
                    }
                }
            }
            IDbDataAdapter adapter = this.DbFactory.CreateDataAdapter(this.DbComm);
            adapter.Fill(dataSet);
            return dataSet;
        }
        #endregion

        public void Dispose()
        {
            if (this._dbComm != null)
            {
                this._dbComm.Dispose();
            }
            if (this._dbTransaction != null)
            {
                this._dbTransaction.Dispose();
            }
            // 关闭数据库连接
            if (this._dbConn != null)
            {
                if (this._dbConn.State != ConnectionState.Closed)
                {
                    this._dbConn.Close();
                    this._dbConn.Dispose();
                }
            }
            this._dbConn = null;
        }
        #endregion
    }

    public enum DBProviderType
    {
        SQL,
        SQLite,
        OleDB,
        Oracle,
        MySQL//安装mysql dotnet版
    }
}
